import json
import time
import os
from flask import Blueprint, request, send_file
# from pyecharts import options as opts
# from pyecharts.charts import Line3D
# from pyecharts.commons.utils import JsCode
from utilsapp.mysql_con import db_connect, db_pagelist, db_table_list, db_fetchone, db_list
from utilsapp import common, utils
spec = Blueprint('spec', __name__)


@spec.route("/time/getlist", methods=["post"])
def time_getlist():
    sensor_id = int(request.json.get("id"))
    startTime = str(time.strftime("%Y-%m-%d %H:%M:%S",
                    time.localtime(int(request.json.get("startTime")) / 1000)))
    endTime = str(time.strftime("%Y-%m-%d %H:%M:%S",
                  time.localtime(int(request.json.get("endTime")) / 1000)))
    yn = startTime.split(" ")[0][:7].replace("-", "")
    s0 = f"select * from sensor where id={sensor_id}"
    res = db_connect(s0)
    if not res.get("data"):
        return {"code": -1, "data": [], "msg": "数据为空!"}
    else:
        port = res.get("data")[0]["port"]
        address = res.get("data")[0]["address"]
        dbname = 's_' + port + '_' + address + '_' + yn
        s1 = f"select distinct DATE_FORMAT(time,'%Y-%m-%d %H:%i:%s') as time from {dbname} where time>'{startTime}' and time<'{endTime}'  order by time asc"

        try:
            res1 = db_connect(s1, _conn='conn')
            ll = len(res1.get("data"))
            for i in range(1, ll + 1):
                res1.get("data")[i - 1]["id"] = i
            return res1
        except:
            return {"code": -1, "data": [], "msg": "数据为空!"}


@spec.route("/domain/getlist", methods=['post'])
def domain_getlist():
    sensor_id = int(request.json.get("id"))
    timetag = request.json.get("time")
    yn = timetag.split(" ")[0][:7].replace("-", "")
    s0 = f"select * from sensor where id={sensor_id}"
    res = db_connect(s0)
    if not res.get("data"):
        return {"code": -1, "data": [], "msg": "数据为空!"}
    else:
        sql_1 = f"""SELECT single from sensor_type st LEFT JOIN sensor s on 
        st.id = s.sensor_type where s.id = {sensor_id}"""
        single = db_fetchone(sql_1)['data']
        port = res.get("data")[0]["port"]
        address = res.get("data")[0]["address"]
        direction = res.get("data")[0]["direction"]
        dbname = 's_' + port + '_' + address + '_' + yn

        s1 = f"select * from {dbname} where time='{timetag}'"
        try:
            res1 = db_connect(s1, _conn='conn')
            ll = len(res1.get("data"))
            ret = {}
            max_len = 0
            for i in res1.get("data"):
              if "rms" not in i['feature_key']:
                temp_data = json.loads(i["value"])
                # 如果是单轴的，把 z 根据安装方向进行转换
                if single ==1:
                    if direction == 'A':
                        i["feature_key"] = i["feature_key"].replace("z", "x")
                        i["feature_key"] = i["feature_key"].replace("Z", "X")
                    elif direction == 'H':
                        i["feature_key"] = i["feature_key"].replace("z", "y")
                        i["feature_key"] = i["feature_key"].replace("Z", "Y")
                else:
                    pass
                ret[i["feature_key"]] = {
                    "data": [round(t, 6) if isinstance(t, (float, int)) else t for t in temp_data]
                }
                max_len = len(temp_data) if len(temp_data) > max_len else max_len
            ret['maxlen'] = max_len
            res1["data"] = ret

            return res1
        except Exception as e:
            return {"code": -1, "data": [], "msg": "数据为空!","err":str(e)}


@spec.route("/domain/getlist_", methods=["post"])
def domain_getlist_1():
    a = request.json
    id = common.assign_value(a, "id")
    time = common.assign_value(a, "time")
    sql = f"select address , port from sensor where id = {id}"
    res = db_list(sql)
    address = res['data'][0]['address']
    port = res['data'][0]['port']
    date = common.str_to_date(time)
    month = common.date_to_str(date, "%Y%m")
    tb_name = f's_{port}_{address}_{month}'
    sql_ = f"select * from {tb_name} where time = '{time}'"

    return db_list(sql_)


# 获取指定sensor需要分析的特征值
@spec.route('spectral/tags', methods=['get'])
def device_tags():
    id = int(request.values.get("type_id"))
    if id != 1:
        id = 3
    s0 = f'''SELECT e.id as id,e.name as value,unit,nickname from eigenvalue e 
    inner JOIN sensor s on find_in_set(CAST(e.id as CHAR),s.eigenvalue) where s.id={id}'''
    s1 = f'''SELECT e.* from sensor s left join sensor_type sy on s.sensor_type=sy.id
     left JOIN eigenvalue e on find_in_set(CAST(e.id as CHAR),sy.eigenvalue) where s.id={id} '''
    s2 = f'''select * from sensor_wave_type where sensor_type_id={id}'''
    ret = db_connect(s2)
    return ret


# 获取最近的时间点
@spec.route('recent/time', methods=['post'])
def recent_time():
    id = int(request.json.get("id"))
    time = request.json.get("time")
    s1 = f"select * from sensor where id={id} limit 1"
    res = db_connect(s1)
    if res["data"]:
        address = res["data"][0]["address"]
        port = res["data"][0]["port"]
        db_name = f"s_{port}_{address}_{time.split(' ')[0][:7].replace('-', '')}"
        s2 = f"select DATE_FORMAT(time,'%Y-%m-%d %H:%i:%s') as time from {db_name} where time<'{time}' ORDER BY time DESC  limit 1"
        res1 = db_connect(s2, _conn='conn')
        if res1["data"]:
            res1["data"] = res1["data"][0]
            return res1
        else:
            return {"code": -1, "msg": "无时间点"}

    else:
        return {"code": -1, "msg": "无测点"}



@spec.route('domain/listbyfeature', methods=['post'])
def get_points():
    sid = request.json['id']
    etime = request.json['time']
    ptype = request.json['feature_key']
    limit = request.json['limit']
    dbres = db_table_list('sensor', 'WHERE id=%s', {'id': sid})
    if dbres['code'] != 0 or len(dbres['data']) <= 0:
        return utils.ok([])
    s = dbres["data"][0]
    date = common.str_to_date(etime)
    month = common.date_to_str(date, "%Y%m")
    tb_name = f's_{s["port"]}_{s["address"]}_{month}'


    sql  = f'''
	SELECT DISTINCT `time`,feature_key,value FROM {tb_name}
        where feature_key='{ptype}' and time <= '{etime}' ORDER BY time desc LIMIT 0,{limit}
'''
    sql_num =f'''select count(time) as num from ({sql}) a'''
    res = db_connect(sql_num,_conn='conn')

    num = res['data'][0]["num"]

    resdb = db_list(sql, _conn='conn')
    for t in resdb['data']:
        t['value'] = json.loads(t['value'])
        t['times'] = common.get_time_arr(
            str(t['time']), len(t['value']), 12800)

    if int(num) < limit:
        date = common.str_to_date_disparity(etime,1)
        month = common.date_to_str(date, "%Y%m")
        tb_name = f's_{s["port"]}_{s["address"]}_{month}'
        sql1=f'''SELECT DISTINCT `time`,feature_key,value FROM {tb_name}
        where feature_key='{ptype}' and time <= '{etime}' ORDER BY time desc LIMIT 0,{limit - num}'''
        res1 = db_list(sql1,_conn='')
        for t in res1['data']:
            t['value'] = json.loads(t['value'])
            t['times'] = common.get_time_arr(
            str(t['time']), len(t['value']), 12800)
        resdb['data'].extend(res1['data'])
    return resdb


# 轴心轨迹
@spec.route('dispalcementwave', methods=['post'])
def dispalcementwave():
    id_ = request.json["id"]
    time = request.json['time']

    s0 = f"select * from sensor where id={id_}"

    res = db_connect(s0)
    port = res.get("data")[0]["port"]
    address = res.get("data")[0]["address"]
    yn = time.split(" ")[0][:7].replace("-", "")
    dbname = 's_' + port + '_' + address + '_' + yn
    sql = f'''
    SELECT * FROM {dbname}
    where time = '{time}' 
    and feature_key in ('sensor_shockX_Dispalcementwave','sensor_shockY_Dispalcementwave','sensor_shockZ_Dispalcementwave')
    '''
    return db_list(sql, _conn='conn')


# 轴心轨迹
@spec.route('dispalcementwave_', methods=['post'])
def aaa():
    id_ = request.json["id"]
    time = request.json['time']
    time_bef = request.json['time_bef']
    s0 = f"select * from sensor where id={id_}"
    res = db_connect(s0)
    port = res.get("data")[0]["port"]
    address = res.get("data")[0]["address"]
    yn = time.split(" ")[0][:7].replace("-", "")
    dbname = 's_' + port + '_' + address + '_' + yn
    sql = f''' SELECT * from {dbname} where id in ( SELECT  max(id) from {dbname} where feature_key like "%vel_wave%" and time in ("{time}","{time_bef}")
    GROUP BY time )'''

    return db_list(sql, _conn='conn')


@spec.route('domain/tags', methods=['post'])
def a():
    id_ = request.json['id']
    sql = f"""SELECT a.* FROM sensor_wave_type a
       INNER JOIN sensor_type b on FIND_IN_SET(cast(a.id as char),b.domain)
       INNER JOIN sensor c on b.id=c.sensor_type
       where c.id={id_}"""
    res = db_list(sql)
    sql_1 = f"""SELECT single from sensor_type st LEFT JOIN sensor s on 
    st.id = s.sensor_type where s.id = {id_}"""
    single = db_fetchone(sql_1)['data']
    if single ==1:
        sql_2 = f"""SELECT direction from sensor where id = {id_}"""
        direction = db_fetchone(sql_2)['data']
        data = res['data']
        i = ''
        if direction == "A":
            i = "轴向"
        elif direction == "H":
            i = "水平"
        else:
            i = "垂直"
        res['data'] = [d for d in data if any(keyword in d['value'] for keyword in [i, "电流", "circular图","轴心轨迹","极坐标","伯德图"])]
    return res



@spec.route('domain/tags/list', methods=['get'])
def a2():
    return db_table_list('sensor_wave_type')


@spec.route("/time/getlist_", methods=['post'])
def a3():
    a = request.json
    id_ = common.assign_value(a, "id")
    time = common.assign_value(a, "time")
    page = common.assign_value(a, "page")
    size = 20


    sql = f"select address,port from sensor where id = {id_}"
  
    res = db_list(sql)
 
    address = res['data'][0]['address']
    port = res['data'][0]['port']
    tb_name = f's_{port}_{address}_{time}'
    sql_ = f"select DISTINCT time from {tb_name} order by time desc"
  
    return db_pagelist(sql_, page, size, 'conn')


